Spreadsheet II
Spreadsheet I showed how you could use the onChange property to stimulate
recalculation of a spreadsheet-like form, although you'd have to re-program
the script to change how the spreadsheet calculated cell results. This
recipe goes one step further to create a full-fledged programmable spreadsheet.
Enter formulas starting with an equals sign ('='). Reference cells by column
first, i.e. "A5".
Discussion
This version of the spreadsheet is a tribute to what can be done with the JavaScript eval()
function. The script looks at each cell formula, and decides which symbols are cell references and which are
numbers (or intrinsic functions--see below). When it finds a cell reference it gets the value of the cell and
substitutes it into the formula, and finally submits the whole expanded string to eval()
for analysis.
Like the first spreadsheet, this one is sized by the use of the sheetHeight and sheetWidth JavaScript variables which are
programmed into this document.
You can enter formulas and numbers right into the cells; precede formulas with an equals sign ('=').
To view a cell's formula, click in the cell
and the formula will appear in the view bar at the top (you can use cut and paste editing to change the formula and put it back in the cell). Use <Tab> to move between cells and complete
editing rather than <Enter>.
Details
There are several rules to remember when using this spreadsheet:
- Only numbers and formulas may be entered into cells.
- You can't enter circular references in formulas. If cell C1 references cell A1, then the
formula for cell A1 can't reference cell C1. This applies no matter how long the "loop."
(You can try to enter circular references, but the spreadsheet won't calculate them.)
- The cell values are recalculated right-to-left, bottom-to-top. An out-of-order evaluation
will occur when a cell is referenced by another cell; the referenced cell will be evaluated
and reassigned before the referencing cell's evaluation is complete.
- This spreadsheet uses a technique called "recursion" while recalculating the spreadsheet.
If a formula becomes too complex (it references cells which reference cells which reference cells...)
the JavaScript engine may crash trying to calculate the result.
- The formula parser isn't terribly smart, and will become unhappy if you reference cells
improperly. The available cells are A1 through E5, and "A01" is definitely not going
to be evaluated correctly. Embedded spaces in cell references ("A 4") are not allowed, either.
- The following mathematical functions and properties are available:
| Math.abs(num) |
Math.acos(angle) |
Math.asin(angle) |
Math.atan(angle) |
Math.atan2(x,y) |
| Math.ceil(num) |
Math.cos(angle) |
Math.exp(num) |
Math.floor(num) |
Math.log(num) |
| Math.max(num,num) |
Math.min(num,num) |
Math.pow(base,exponent) |
Math.round(num) |
Math.sin(angle) |
| Math.sqrt(num) |
Math.tan(angle) |
Math.E |
Math.LN2 |
Math.LN10 |
| Math.LOG2E |
Math.LOG10E |
Math.PI |
Math.SQRT1_2 |
Math.SQRT2 |
The names should be entered just as they are in the table; replace italicized arguments with
your own numbers and/or cell references. (These are formula elements; if you want to set a cell
to the value of pi, for example, use the formula "=Math.PI".)
Just in case you try to use this to do your taxes:
Use at your own risk. No warranty is given or implied of the suitability
of this spreadsheet applet for any specific application. Neither the authors
nor the publisher will be held responsible for any unwanted effects due to the
use of this applet or any derivative.
Copyright ©1998 by Charles River Media, All Rights Reserved